<!DOCTYPE html>
<html lang="en-US">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>sqlx库使用指南 | 小狐档案库</title>
    <meta name="generator" content="VuePress 1.8.2">
    <link rel="icon" href="/favicon.ico">
    <meta name="description" content="学习资料">
    <meta name="viewport" content="width=device-width,initial-scale=1,user-scalable=no">
    
    <link rel="preload" href="/assets/css/0.styles.0c36f186.css" as="style"><link rel="preload" href="/assets/js/app.7029ddab.js" as="script"><link rel="preload" href="/assets/js/3.ebaa3085.js" as="script"><link rel="preload" href="/assets/js/1.8ce67e8c.js" as="script"><link rel="preload" href="/assets/js/41.007ae34c.js" as="script"><link rel="prefetch" href="/assets/js/10.15c1f9c8.js"><link rel="prefetch" href="/assets/js/11.dc2fbddf.js"><link rel="prefetch" href="/assets/js/12.26ab1f36.js"><link rel="prefetch" href="/assets/js/13.6e1d4dbc.js"><link rel="prefetch" href="/assets/js/14.0cd48e80.js"><link rel="prefetch" href="/assets/js/15.74a9f029.js"><link rel="prefetch" href="/assets/js/16.24ef3382.js"><link rel="prefetch" href="/assets/js/17.c8a9bd0f.js"><link rel="prefetch" href="/assets/js/18.fbd494c2.js"><link rel="prefetch" href="/assets/js/19.f9a0df71.js"><link rel="prefetch" href="/assets/js/20.e6d0df38.js"><link rel="prefetch" href="/assets/js/21.932645cc.js"><link rel="prefetch" href="/assets/js/22.d6027dce.js"><link rel="prefetch" href="/assets/js/23.53bce141.js"><link rel="prefetch" href="/assets/js/24.ed3b8b64.js"><link rel="prefetch" href="/assets/js/25.ed88877e.js"><link rel="prefetch" href="/assets/js/26.f5ad24d5.js"><link rel="prefetch" href="/assets/js/27.64ad45b4.js"><link rel="prefetch" href="/assets/js/28.f308f8ab.js"><link rel="prefetch" href="/assets/js/29.4d95742d.js"><link rel="prefetch" href="/assets/js/30.0a867de0.js"><link rel="prefetch" href="/assets/js/31.f12def86.js"><link rel="prefetch" href="/assets/js/32.6b4fd8b7.js"><link rel="prefetch" href="/assets/js/33.4bb2aa9c.js"><link rel="prefetch" href="/assets/js/34.013e731c.js"><link rel="prefetch" href="/assets/js/35.6b263ef8.js"><link rel="prefetch" href="/assets/js/36.06acf3cc.js"><link rel="prefetch" href="/assets/js/37.fe72c29c.js"><link rel="prefetch" href="/assets/js/38.9b4e0bd6.js"><link rel="prefetch" href="/assets/js/39.0cde0408.js"><link rel="prefetch" href="/assets/js/4.9b5650bb.js"><link rel="prefetch" href="/assets/js/40.56bda9a8.js"><link rel="prefetch" href="/assets/js/42.bdaf414b.js"><link rel="prefetch" href="/assets/js/43.78d32d4e.js"><link rel="prefetch" href="/assets/js/44.a10cf8a5.js"><link rel="prefetch" href="/assets/js/45.19e14721.js"><link rel="prefetch" href="/assets/js/46.57ebcf06.js"><link rel="prefetch" href="/assets/js/47.9a513ffe.js"><link rel="prefetch" href="/assets/js/48.245b165a.js"><link rel="prefetch" href="/assets/js/49.fab43655.js"><link rel="prefetch" href="/assets/js/5.a16ef7f8.js"><link rel="prefetch" href="/assets/js/50.48c191fd.js"><link rel="prefetch" href="/assets/js/51.28a94b0e.js"><link rel="prefetch" href="/assets/js/52.e783fbf9.js"><link rel="prefetch" href="/assets/js/53.2f0e960e.js"><link rel="prefetch" href="/assets/js/54.577ff54e.js"><link rel="prefetch" href="/assets/js/55.4bb99598.js"><link rel="prefetch" href="/assets/js/56.e820e954.js"><link rel="prefetch" href="/assets/js/57.0cea0cfa.js"><link rel="prefetch" href="/assets/js/58.e31cfcd0.js"><link rel="prefetch" href="/assets/js/59.458ab168.js"><link rel="prefetch" href="/assets/js/6.7da424ba.js"><link rel="prefetch" href="/assets/js/60.e9caace2.js"><link rel="prefetch" href="/assets/js/61.0b61afdb.js"><link rel="prefetch" href="/assets/js/62.c240acab.js"><link rel="prefetch" href="/assets/js/63.71cd2a97.js"><link rel="prefetch" href="/assets/js/64.977ea29b.js"><link rel="prefetch" href="/assets/js/65.79b2d62a.js"><link rel="prefetch" href="/assets/js/66.8da69071.js"><link rel="prefetch" href="/assets/js/67.fdc57d7e.js"><link rel="prefetch" href="/assets/js/68.d43eabe9.js"><link rel="prefetch" href="/assets/js/7.6e92edd1.js"><link rel="prefetch" href="/assets/js/8.2389170b.js"><link rel="prefetch" href="/assets/js/9.77841f24.js">
    <link rel="stylesheet" href="/assets/css/0.styles.0c36f186.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="theme-container" data-v-1156296a><div data-v-1156296a><div id="loader-wrapper" class="loading-wrapper" data-v-d48f4d20 data-v-1156296a data-v-1156296a><div class="loader-main" data-v-d48f4d20><div data-v-d48f4d20></div><div data-v-d48f4d20></div><div data-v-d48f4d20></div><div data-v-d48f4d20></div></div> <!----> <!----></div> <div class="password-shadow password-wrapper-out" style="display:none;" data-v-4e82dffc data-v-1156296a data-v-1156296a><h3 class="title" data-v-4e82dffc data-v-4e82dffc>小狐档案库</h3> <p class="description" data-v-4e82dffc data-v-4e82dffc>学习资料</p> <label id="box" class="inputBox" data-v-4e82dffc data-v-4e82dffc><input type="password" value="" data-v-4e82dffc> <span data-v-4e82dffc>Konck! Knock!</span> <button data-v-4e82dffc>OK</button></label> <div class="footer" data-v-4e82dffc data-v-4e82dffc><span data-v-4e82dffc><i class="iconfont reco-theme" data-v-4e82dffc></i> <a target="blank" href="https://vuepress-theme-reco.recoluan.com" data-v-4e82dffc>vuePress-theme-reco</a></span> <span data-v-4e82dffc><i class="iconfont reco-copyright" data-v-4e82dffc></i> <a data-v-4e82dffc><span data-v-4e82dffc>小狐</span>
            
          <span data-v-4e82dffc>2021-9-1 - </span>
          2021
        </a></span></div></div> <div class="hide" data-v-1156296a><header class="navbar" data-v-1156296a><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><img src="/logo.png" alt="小狐档案库" class="logo"> <span class="site-name">小狐档案库</span></a> <div class="links"><div class="color-picker"><a class="color-button"><i class="iconfont reco-color"></i></a> <div class="color-picker-menu" style="display:none;"><div class="mode-options"><h4 class="title">Choose mode</h4> <ul class="color-mode-options"><li class="dark">dark</li><li class="auto active">auto</li><li class="light">light</li></ul></div></div></div> <div class="search-box"><i class="iconfont reco-search"></i> <input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/" class="nav-link"><i class="iconfont reco-home"></i>
  首页
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-category"></i>
      分类
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/categories/GO/" class="nav-link"><i class="undefined"></i>
  GO
</a></li><li class="dropdown-item"><!----> <a href="/categories/面试系列/" class="nav-link"><i class="undefined"></i>
  面试系列
</a></li></ul></div></div><div class="nav-item"><a href="/tag/" class="nav-link"><i class="iconfont reco-tag"></i>
  Tag
</a></div><div class="nav-item"><a href="/timeline/" class="nav-link"><i class="iconfont reco-date"></i>
  足迹
</a></div><div class="nav-item"><a href="https://www.xiaohu.team" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-message"></i>
  摇光图库
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></div> <!----></nav></div></header> <div class="sidebar-mask" data-v-1156296a></div> <aside class="sidebar" data-v-1156296a><div class="personal-info-wrapper" data-v-828910c6 data-v-1156296a><img src="/avatar.jpg" alt="author-avatar" class="personal-img" data-v-828910c6> <h3 class="name" data-v-828910c6>
    小狐
  </h3> <div class="num" data-v-828910c6><div data-v-828910c6><h3 data-v-828910c6>58</h3> <h6 data-v-828910c6>Articles</h6></div> <div data-v-828910c6><h3 data-v-828910c6>20</h3> <h6 data-v-828910c6>Tags</h6></div></div> <ul class="social-links" data-v-828910c6></ul> <hr data-v-828910c6></div> <nav class="nav-links"><div class="nav-item"><a href="/" class="nav-link"><i class="iconfont reco-home"></i>
  首页
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-category"></i>
      分类
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/categories/GO/" class="nav-link"><i class="undefined"></i>
  GO
</a></li><li class="dropdown-item"><!----> <a href="/categories/面试系列/" class="nav-link"><i class="undefined"></i>
  面试系列
</a></li></ul></div></div><div class="nav-item"><a href="/tag/" class="nav-link"><i class="iconfont reco-tag"></i>
  Tag
</a></div><div class="nav-item"><a href="/timeline/" class="nav-link"><i class="iconfont reco-date"></i>
  足迹
</a></div><div class="nav-item"><a href="https://www.xiaohu.team" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-message"></i>
  摇光图库
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></div> <!----></nav> <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>go语言学习</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><section class="sidebar-group collapsable is-sub-group depth-1"><p class="sidebar-heading"><span>go语言基础学习</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable is-sub-group depth-1"><p class="sidebar-heading"><span>gin</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable is-sub-group depth-1"><p class="sidebar-heading open"><span>数据库操作</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/blogs/GO/go_mysql/go_mysql.html" class="sidebar-link">Go语言操作MySQL</a></li><li><a href="/blogs/GO/go_mysql/go_sqlx.html" aria-current="page" class="active sidebar-link">sqlx库使用指南</a></li><li><a href="/blogs/GO/go_mysql/go_sqlx_2.html" class="sidebar-link">GO使用sqlx批量插入数据的若干方法</a></li></ul></section></li><li><section class="sidebar-group collapsable is-sub-group depth-1"><p class="sidebar-heading"><span>gorm</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable is-sub-group depth-1"><p class="sidebar-heading"><span>gorm</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable is-sub-group depth-1"><p class="sidebar-heading"><span>NoSql</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable is-sub-group depth-1"><p class="sidebar-heading"><span>消息队列</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable is-sub-group depth-1"><p class="sidebar-heading"><span>go三方库</span> <span class="arrow right"></span></p> <!----></section></li></ul></section></li></ul> </aside> <div class="password-shadow password-wrapper-in" style="display:none;" data-v-4e82dffc data-v-1156296a><h3 class="title" data-v-4e82dffc data-v-4e82dffc>sqlx库使用指南</h3> <!----> <label id="box" class="inputBox" data-v-4e82dffc data-v-4e82dffc><input type="password" value="" data-v-4e82dffc> <span data-v-4e82dffc>Konck! Knock!</span> <button data-v-4e82dffc>OK</button></label> <div class="footer" data-v-4e82dffc data-v-4e82dffc><span data-v-4e82dffc><i class="iconfont reco-theme" data-v-4e82dffc></i> <a target="blank" href="https://vuepress-theme-reco.recoluan.com" data-v-4e82dffc>vuePress-theme-reco</a></span> <span data-v-4e82dffc><i class="iconfont reco-copyright" data-v-4e82dffc></i> <a data-v-4e82dffc><span data-v-4e82dffc>小狐</span>
            
          <span data-v-4e82dffc>2021-9-1 - </span>
          2021
        </a></span></div></div> <div data-v-1156296a><main class="page"><section><div class="page-title"><h1 class="title">sqlx库使用指南</h1> <div data-v-1ff7123e><i class="iconfont reco-account" data-v-1ff7123e><span data-v-1ff7123e>小狐</span></i> <i class="iconfont reco-date" data-v-1ff7123e><span data-v-1ff7123e>9/11/2021</span></i> <!----> <i class="tags iconfont reco-tag" data-v-1ff7123e><span class="tag-item" data-v-1ff7123e>GO</span><span class="tag-item" data-v-1ff7123e>数据库操作</span><span class="tag-item" data-v-1ff7123e>MySQL</span></i></div></div> <div class="theme-reco-content content__default"><h1 id="sqlx库使用指南"><a href="#sqlx库使用指南" class="header-anchor">#</a> sqlx库使用指南</h1> <p>在项目中我们通常可能会使用<code>database/sql</code>连接MySQL数据库。本文借助使用<code>sqlx</code>实现批量插入数据的例子，介绍了<code>sqlx</code>中可能被你忽视了的<code>sqlx.In</code>和<code>DB.NamedExec</code>方法。</p> <h2 id="sqlx介绍"><a href="#sqlx介绍" class="header-anchor">#</a> sqlx介绍</h2> <p>在项目中我们通常可能会使用<code>database/sql</code>连接MySQL数据库。<code>sqlx</code>可以认为是Go语言内置<code>database/sql</code>的超集，它在优秀的内置<code>database/sql</code>基础上提供了一组扩展。这些扩展中除了大家常用来查询的<code>Get(dest interface{}, ...) error</code>和<code>Select(dest interface{}, ...) error</code>外还有很多其他强大的功能。</p> <h2 id="安装sqlx"><a href="#安装sqlx" class="header-anchor">#</a> 安装sqlx</h2> <div class="language-go line-numbers-mode"><pre class="language-go"><code><span class="token keyword">go</span> get github<span class="token punctuation">.</span>com<span class="token operator">/</span>jmoiron<span class="token operator">/</span>sqlx
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h2 id="基本使用"><a href="#基本使用" class="header-anchor">#</a> 基本使用</h2> <h3 id="连接数据库"><a href="#连接数据库" class="header-anchor">#</a> 连接数据库</h3> <div class="language-go line-numbers-mode"><pre class="language-go"><code><span class="token keyword">var</span> db <span class="token operator">*</span>sqlx<span class="token punctuation">.</span>DB

<span class="token keyword">func</span> <span class="token function">initDB</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">(</span>err <span class="token builtin">error</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	dsn <span class="token operator">:=</span> <span class="token string">&quot;user:password@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&amp;parseTime=True&quot;</span>
	<span class="token comment">// 也可以使用MustConnect连接不成功就panic</span>
	db<span class="token punctuation">,</span> err <span class="token operator">=</span> sqlx<span class="token punctuation">.</span><span class="token function">Connect</span><span class="token punctuation">(</span><span class="token string">&quot;mysql&quot;</span><span class="token punctuation">,</span> dsn<span class="token punctuation">)</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;connect DB failed, err:%v\n&quot;</span><span class="token punctuation">,</span> err<span class="token punctuation">)</span>
		<span class="token keyword">return</span>
	<span class="token punctuation">}</span>
	db<span class="token punctuation">.</span><span class="token function">SetMaxOpenConns</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>
	db<span class="token punctuation">.</span><span class="token function">SetMaxIdleConns</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span>
	<span class="token keyword">return</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br></div></div><h3 id="查询"><a href="#查询" class="header-anchor">#</a> 查询</h3> <p>查询单行数据示例代码如下：</p> <div class="language-go line-numbers-mode"><pre class="language-go"><code><span class="token comment">// 查询单条数据示例</span>
<span class="token keyword">func</span> <span class="token function">queryRowDemo</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	sqlStr <span class="token operator">:=</span> <span class="token string">&quot;select id, name, age from user where id=?&quot;</span>
	<span class="token keyword">var</span> u user
	err <span class="token operator">:=</span> db<span class="token punctuation">.</span><span class="token function">Get</span><span class="token punctuation">(</span><span class="token operator">&amp;</span>u<span class="token punctuation">,</span> sqlStr<span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;get failed, err:%v\n&quot;</span><span class="token punctuation">,</span> err<span class="token punctuation">)</span>
		<span class="token keyword">return</span>
	<span class="token punctuation">}</span>
	fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;id:%d name:%s age:%d\n&quot;</span><span class="token punctuation">,</span> u<span class="token punctuation">.</span>ID<span class="token punctuation">,</span> u<span class="token punctuation">.</span>Name<span class="token punctuation">,</span> u<span class="token punctuation">.</span>Age<span class="token punctuation">)</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br></div></div><p>查询多行数据示例代码如下：</p> <div class="language-go line-numbers-mode"><pre class="language-go"><code><span class="token comment">// 查询多条数据示例</span>
<span class="token keyword">func</span> <span class="token function">queryMultiRowDemo</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	sqlStr <span class="token operator">:=</span> <span class="token string">&quot;select id, name, age from user where id &gt; ?&quot;</span>
	<span class="token keyword">var</span> users <span class="token punctuation">[</span><span class="token punctuation">]</span>user
	err <span class="token operator">:=</span> db<span class="token punctuation">.</span><span class="token function">Select</span><span class="token punctuation">(</span><span class="token operator">&amp;</span>users<span class="token punctuation">,</span> sqlStr<span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;query failed, err:%v\n&quot;</span><span class="token punctuation">,</span> err<span class="token punctuation">)</span>
		<span class="token keyword">return</span>
	<span class="token punctuation">}</span>
	fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;users:%#v\n&quot;</span><span class="token punctuation">,</span> users<span class="token punctuation">)</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br></div></div><h3 id="插入、更新和删除"><a href="#插入、更新和删除" class="header-anchor">#</a> 插入、更新和删除</h3> <p>sqlx中的exec方法与原生sql中的exec使用基本一致：</p> <div class="language-go line-numbers-mode"><pre class="language-go"><code><span class="token comment">// 插入数据</span>
<span class="token keyword">func</span> <span class="token function">insertRowDemo</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	sqlStr <span class="token operator">:=</span> <span class="token string">&quot;insert into user(name, age) values (?,?)&quot;</span>
	ret<span class="token punctuation">,</span> err <span class="token operator">:=</span> db<span class="token punctuation">.</span><span class="token function">Exec</span><span class="token punctuation">(</span>sqlStr<span class="token punctuation">,</span> <span class="token string">&quot;沙河小王子&quot;</span><span class="token punctuation">,</span> <span class="token number">19</span><span class="token punctuation">)</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;insert failed, err:%v\n&quot;</span><span class="token punctuation">,</span> err<span class="token punctuation">)</span>
		<span class="token keyword">return</span>
	<span class="token punctuation">}</span>
	theID<span class="token punctuation">,</span> err <span class="token operator">:=</span> ret<span class="token punctuation">.</span><span class="token function">LastInsertId</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token comment">// 新插入数据的id</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;get lastinsert ID failed, err:%v\n&quot;</span><span class="token punctuation">,</span> err<span class="token punctuation">)</span>
		<span class="token keyword">return</span>
	<span class="token punctuation">}</span>
	fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;insert success, the id is %d.\n&quot;</span><span class="token punctuation">,</span> theID<span class="token punctuation">)</span>
<span class="token punctuation">}</span>

<span class="token comment">// 更新数据</span>
<span class="token keyword">func</span> <span class="token function">updateRowDemo</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	sqlStr <span class="token operator">:=</span> <span class="token string">&quot;update user set age=? where id = ?&quot;</span>
	ret<span class="token punctuation">,</span> err <span class="token operator">:=</span> db<span class="token punctuation">.</span><span class="token function">Exec</span><span class="token punctuation">(</span>sqlStr<span class="token punctuation">,</span> <span class="token number">39</span><span class="token punctuation">,</span> <span class="token number">6</span><span class="token punctuation">)</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;update failed, err:%v\n&quot;</span><span class="token punctuation">,</span> err<span class="token punctuation">)</span>
		<span class="token keyword">return</span>
	<span class="token punctuation">}</span>
	n<span class="token punctuation">,</span> err <span class="token operator">:=</span> ret<span class="token punctuation">.</span><span class="token function">RowsAffected</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token comment">// 操作影响的行数</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;get RowsAffected failed, err:%v\n&quot;</span><span class="token punctuation">,</span> err<span class="token punctuation">)</span>
		<span class="token keyword">return</span>
	<span class="token punctuation">}</span>
	fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;update success, affected rows:%d\n&quot;</span><span class="token punctuation">,</span> n<span class="token punctuation">)</span>
<span class="token punctuation">}</span>

<span class="token comment">// 删除数据</span>
<span class="token keyword">func</span> <span class="token function">deleteRowDemo</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	sqlStr <span class="token operator">:=</span> <span class="token string">&quot;delete from user where id = ?&quot;</span>
	ret<span class="token punctuation">,</span> err <span class="token operator">:=</span> db<span class="token punctuation">.</span><span class="token function">Exec</span><span class="token punctuation">(</span>sqlStr<span class="token punctuation">,</span> <span class="token number">6</span><span class="token punctuation">)</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;delete failed, err:%v\n&quot;</span><span class="token punctuation">,</span> err<span class="token punctuation">)</span>
		<span class="token keyword">return</span>
	<span class="token punctuation">}</span>
	n<span class="token punctuation">,</span> err <span class="token operator">:=</span> ret<span class="token punctuation">.</span><span class="token function">RowsAffected</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token comment">// 操作影响的行数</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;get RowsAffected failed, err:%v\n&quot;</span><span class="token punctuation">,</span> err<span class="token punctuation">)</span>
		<span class="token keyword">return</span>
	<span class="token punctuation">}</span>
	fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;delete success, affected rows:%d\n&quot;</span><span class="token punctuation">,</span> n<span class="token punctuation">)</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br><span class="line-number">42</span><br><span class="line-number">43</span><br><span class="line-number">44</span><br><span class="line-number">45</span><br><span class="line-number">46</span><br><span class="line-number">47</span><br></div></div><h3 id="namedexec"><a href="#namedexec" class="header-anchor">#</a> NamedExec</h3> <p><code>DB.NamedExec</code>方法用来绑定SQL语句与结构体或map中的同名字段。</p> <div class="language-go line-numbers-mode"><pre class="language-go"><code><span class="token keyword">func</span> <span class="token function">insertUserDemo</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">(</span>err <span class="token builtin">error</span><span class="token punctuation">)</span><span class="token punctuation">{</span>
	sqlStr <span class="token operator">:=</span> <span class="token string">&quot;INSERT INTO user (name,age) VALUES (:name,:age)&quot;</span>
	<span class="token boolean">_</span><span class="token punctuation">,</span> err <span class="token operator">=</span> db<span class="token punctuation">.</span><span class="token function">NamedExec</span><span class="token punctuation">(</span>sqlStr<span class="token punctuation">,</span>
		<span class="token keyword">map</span><span class="token punctuation">[</span><span class="token builtin">string</span><span class="token punctuation">]</span><span class="token keyword">interface</span><span class="token punctuation">{</span><span class="token punctuation">}</span><span class="token punctuation">{</span>
			<span class="token string">&quot;name&quot;</span><span class="token punctuation">:</span> <span class="token string">&quot;七米&quot;</span><span class="token punctuation">,</span>
			<span class="token string">&quot;age&quot;</span><span class="token punctuation">:</span> <span class="token number">28</span><span class="token punctuation">,</span>
		<span class="token punctuation">}</span><span class="token punctuation">)</span>
	<span class="token keyword">return</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><h3 id="namedquery"><a href="#namedquery" class="header-anchor">#</a> NamedQuery</h3> <p>与<code>DB.NamedExec</code>同理，这里是支持查询。</p> <div class="language-go line-numbers-mode"><pre class="language-go"><code><span class="token keyword">func</span> <span class="token function">namedQuery</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">{</span>
	sqlStr <span class="token operator">:=</span> <span class="token string">&quot;SELECT * FROM user WHERE name=:name&quot;</span>
	<span class="token comment">// 使用map做命名查询</span>
	rows<span class="token punctuation">,</span> err <span class="token operator">:=</span> db<span class="token punctuation">.</span><span class="token function">NamedQuery</span><span class="token punctuation">(</span>sqlStr<span class="token punctuation">,</span> <span class="token keyword">map</span><span class="token punctuation">[</span><span class="token builtin">string</span><span class="token punctuation">]</span><span class="token keyword">interface</span><span class="token punctuation">{</span><span class="token punctuation">}</span><span class="token punctuation">{</span><span class="token string">&quot;name&quot;</span><span class="token punctuation">:</span> <span class="token string">&quot;七米&quot;</span><span class="token punctuation">}</span><span class="token punctuation">)</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;db.NamedQuery failed, err:%v\n&quot;</span><span class="token punctuation">,</span> err<span class="token punctuation">)</span>
		<span class="token keyword">return</span>
	<span class="token punctuation">}</span>
	<span class="token keyword">defer</span> rows<span class="token punctuation">.</span><span class="token function">Close</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
	<span class="token keyword">for</span> rows<span class="token punctuation">.</span><span class="token function">Next</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">{</span>
		<span class="token keyword">var</span> u user
		err <span class="token operator">:=</span> rows<span class="token punctuation">.</span><span class="token function">StructScan</span><span class="token punctuation">(</span><span class="token operator">&amp;</span>u<span class="token punctuation">)</span>
		<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
			fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;scan failed, err:%v\n&quot;</span><span class="token punctuation">,</span> err<span class="token punctuation">)</span>
			<span class="token keyword">continue</span>
		<span class="token punctuation">}</span>
		fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;user:%#v\n&quot;</span><span class="token punctuation">,</span> u<span class="token punctuation">)</span>
	<span class="token punctuation">}</span>

	u <span class="token operator">:=</span> user<span class="token punctuation">{</span>
		Name<span class="token punctuation">:</span> <span class="token string">&quot;七米&quot;</span><span class="token punctuation">,</span>
	<span class="token punctuation">}</span>
	<span class="token comment">// 使用结构体命名查询，根据结构体字段的 db tag进行映射</span>
	rows<span class="token punctuation">,</span> err <span class="token operator">=</span> db<span class="token punctuation">.</span><span class="token function">NamedQuery</span><span class="token punctuation">(</span>sqlStr<span class="token punctuation">,</span> u<span class="token punctuation">)</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;db.NamedQuery failed, err:%v\n&quot;</span><span class="token punctuation">,</span> err<span class="token punctuation">)</span>
		<span class="token keyword">return</span>
	<span class="token punctuation">}</span>
	<span class="token keyword">defer</span> rows<span class="token punctuation">.</span><span class="token function">Close</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
	<span class="token keyword">for</span> rows<span class="token punctuation">.</span><span class="token function">Next</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">{</span>
		<span class="token keyword">var</span> u user
		err <span class="token operator">:=</span> rows<span class="token punctuation">.</span><span class="token function">StructScan</span><span class="token punctuation">(</span><span class="token operator">&amp;</span>u<span class="token punctuation">)</span>
		<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
			fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;scan failed, err:%v\n&quot;</span><span class="token punctuation">,</span> err<span class="token punctuation">)</span>
			<span class="token keyword">continue</span>
		<span class="token punctuation">}</span>
		fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;user:%#v\n&quot;</span><span class="token punctuation">,</span> u<span class="token punctuation">)</span>
	<span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br></div></div><h3 id="事务操作"><a href="#事务操作" class="header-anchor">#</a> 事务操作</h3> <p>对于事务操作，我们可以使用<code>sqlx</code>中提供的<code>db.Beginx()</code>和<code>tx.Exec()</code>方法。示例代码如下：</p> <div class="language-go line-numbers-mode"><pre class="language-go"><code><span class="token keyword">func</span> <span class="token function">transactionDemo2</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">(</span>err <span class="token builtin">error</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	tx<span class="token punctuation">,</span> err <span class="token operator">:=</span> db<span class="token punctuation">.</span><span class="token function">Beginx</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token comment">// 开启事务</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;begin trans failed, err:%v\n&quot;</span><span class="token punctuation">,</span> err<span class="token punctuation">)</span>
		<span class="token keyword">return</span> err
	<span class="token punctuation">}</span>
	<span class="token keyword">defer</span> <span class="token keyword">func</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
		<span class="token keyword">if</span> p <span class="token operator">:=</span> <span class="token function">recover</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span> p <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
			tx<span class="token punctuation">.</span><span class="token function">Rollback</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
			<span class="token function">panic</span><span class="token punctuation">(</span>p<span class="token punctuation">)</span> <span class="token comment">// re-throw panic after Rollback</span>
		<span class="token punctuation">}</span> <span class="token keyword">else</span> <span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
			fmt<span class="token punctuation">.</span><span class="token function">Println</span><span class="token punctuation">(</span><span class="token string">&quot;rollback&quot;</span><span class="token punctuation">)</span>
			tx<span class="token punctuation">.</span><span class="token function">Rollback</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token comment">// err is non-nil; don't change it</span>
		<span class="token punctuation">}</span> <span class="token keyword">else</span> <span class="token punctuation">{</span>
			err <span class="token operator">=</span> tx<span class="token punctuation">.</span><span class="token function">Commit</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token comment">// err is nil; if Commit returns error update err</span>
			fmt<span class="token punctuation">.</span><span class="token function">Println</span><span class="token punctuation">(</span><span class="token string">&quot;commit&quot;</span><span class="token punctuation">)</span>
		<span class="token punctuation">}</span>
	<span class="token punctuation">}</span><span class="token punctuation">(</span><span class="token punctuation">)</span>

	sqlStr1 <span class="token operator">:=</span> <span class="token string">&quot;Update user set age=20 where id=?&quot;</span>

	rs<span class="token punctuation">,</span> err <span class="token operator">:=</span> tx<span class="token punctuation">.</span><span class="token function">Exec</span><span class="token punctuation">(</span>sqlStr1<span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span>
	<span class="token keyword">if</span> err<span class="token operator">!=</span> <span class="token boolean">nil</span><span class="token punctuation">{</span>
		<span class="token keyword">return</span> err
	<span class="token punctuation">}</span>
	n<span class="token punctuation">,</span> err <span class="token operator">:=</span> rs<span class="token punctuation">.</span><span class="token function">RowsAffected</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		<span class="token keyword">return</span> err
	<span class="token punctuation">}</span>
	<span class="token keyword">if</span> n <span class="token operator">!=</span> <span class="token number">1</span> <span class="token punctuation">{</span>
		<span class="token keyword">return</span> errors<span class="token punctuation">.</span><span class="token function">New</span><span class="token punctuation">(</span><span class="token string">&quot;exec sqlStr1 failed&quot;</span><span class="token punctuation">)</span>
	<span class="token punctuation">}</span>
	sqlStr2 <span class="token operator">:=</span> <span class="token string">&quot;Update user set age=50 where i=?&quot;</span>
	rs<span class="token punctuation">,</span> err <span class="token operator">=</span> tx<span class="token punctuation">.</span><span class="token function">Exec</span><span class="token punctuation">(</span>sqlStr2<span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">)</span>
	<span class="token keyword">if</span> err<span class="token operator">!=</span><span class="token boolean">nil</span><span class="token punctuation">{</span>
		<span class="token keyword">return</span> err
	<span class="token punctuation">}</span>
	n<span class="token punctuation">,</span> err <span class="token operator">=</span> rs<span class="token punctuation">.</span><span class="token function">RowsAffected</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		<span class="token keyword">return</span> err
	<span class="token punctuation">}</span>
	<span class="token keyword">if</span> n <span class="token operator">!=</span> <span class="token number">1</span> <span class="token punctuation">{</span>
		<span class="token keyword">return</span> errors<span class="token punctuation">.</span><span class="token function">New</span><span class="token punctuation">(</span><span class="token string">&quot;exec sqlStr1 failed&quot;</span><span class="token punctuation">)</span>
	<span class="token punctuation">}</span>
	<span class="token keyword">return</span> err
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br><span class="line-number">42</span><br><span class="line-number">43</span><br><span class="line-number">44</span><br><span class="line-number">45</span><br><span class="line-number">46</span><br></div></div><h2 id="sqlx-in"><a href="#sqlx-in" class="header-anchor">#</a> sqlx.In</h2> <p><code>sqlx.In</code>是<code>sqlx</code>提供的一个非常方便的函数。</p> <h3 id="sqlx-in的批量插入示例"><a href="#sqlx-in的批量插入示例" class="header-anchor">#</a> sqlx.In的批量插入示例</h3> <h4 id="表结构"><a href="#表结构" class="header-anchor">#</a> 表结构</h4> <p>为了方便演示插入数据操作，这里创建一个<code>user</code>表，表结构如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span><span class="token keyword">user</span><span class="token punctuation">`</span> <span class="token punctuation">(</span>
    <span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">BIGINT</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    <span class="token punctuation">`</span>name<span class="token punctuation">`</span> <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token string">''</span><span class="token punctuation">,</span>
    <span class="token punctuation">`</span>age<span class="token punctuation">`</span> <span class="token keyword">INT</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token string">'0'</span><span class="token punctuation">,</span>
    <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">InnoDB</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token operator">=</span><span class="token number">1</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8mb4<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><h4 id="结构体"><a href="#结构体" class="header-anchor">#</a> 结构体</h4> <p>定义一个<code>user</code>结构体，字段通过tag与数据库中user表的列一致。</p> <div class="language-go line-numbers-mode"><pre class="language-go"><code><span class="token keyword">type</span> User <span class="token keyword">struct</span> <span class="token punctuation">{</span>
	Name <span class="token builtin">string</span> <span class="token string">`db:&quot;name&quot;`</span>
	Age  <span class="token builtin">int</span>    <span class="token string">`db:&quot;age&quot;`</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h4 id="bindvars-绑定变量"><a href="#bindvars-绑定变量" class="header-anchor">#</a> bindvars（绑定变量）</h4> <p>查询占位符<code>?</code>在内部称为<strong>bindvars（查询占位符）</strong>,它非常重要。你应该始终使用它们向数据库发送值，因为它们可以防止SQL注入攻击。<code>database/sql</code>不尝试对查询文本进行任何验证；它与编码的参数一起按原样发送到服务器。除非驱动程序实现一个特殊的接口，否则在执行之前，查询是在服务器上准备的。因此<code>bindvars</code>是特定于数据库的:</p> <ul><li>MySQL中使用<code>?</code></li> <li>PostgreSQL使用枚举的<code>$1</code>、<code>$2</code>等bindvar语法</li> <li>SQLite中<code>?</code>和<code>$1</code>的语法都支持</li> <li>Oracle中使用<code>:name</code>的语法</li></ul> <p><code>bindvars</code>的一个常见误解是，它们用来在sql语句中插入值。它们其实仅用于参数化，不允许更改SQL语句的结构。例如，使用<code>bindvars</code>尝试参数化列或表名将不起作用：</p> <div class="language-go line-numbers-mode"><pre class="language-go"><code><span class="token comment">// ？不能用来插入表名（做SQL语句中表名的占位符）</span>
db<span class="token punctuation">.</span><span class="token function">Query</span><span class="token punctuation">(</span><span class="token string">&quot;SELECT * FROM ?&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;mytable&quot;</span><span class="token punctuation">)</span>
 
<span class="token comment">// ？也不能用来插入列名（做SQL语句中列名的占位符）</span>
db<span class="token punctuation">.</span><span class="token function">Query</span><span class="token punctuation">(</span><span class="token string">&quot;SELECT ?, ? FROM people&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;name&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;location&quot;</span><span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><h4 id="自己拼接语句实现批量插入"><a href="#自己拼接语句实现批量插入" class="header-anchor">#</a> 自己拼接语句实现批量插入</h4> <p>比较笨，但是很好理解。就是有多少个User就拼接多少个<code>(?, ?)</code>。</p> <div class="language-go line-numbers-mode"><pre class="language-go"><code><span class="token comment">// BatchInsertUsers 自行构造批量插入的语句</span>
<span class="token keyword">func</span> <span class="token function">BatchInsertUsers</span><span class="token punctuation">(</span>users <span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token operator">*</span>User<span class="token punctuation">)</span> <span class="token builtin">error</span> <span class="token punctuation">{</span>
	<span class="token comment">// 存放 (?, ?) 的slice</span>
	valueStrings <span class="token operator">:=</span> <span class="token function">make</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token builtin">string</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">,</span> <span class="token function">len</span><span class="token punctuation">(</span>users<span class="token punctuation">)</span><span class="token punctuation">)</span>
	<span class="token comment">// 存放values的slice</span>
	valueArgs <span class="token operator">:=</span> <span class="token function">make</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token keyword">interface</span><span class="token punctuation">{</span><span class="token punctuation">}</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">,</span> <span class="token function">len</span><span class="token punctuation">(</span>users<span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token number">2</span><span class="token punctuation">)</span>
	<span class="token comment">// 遍历users准备相关数据</span>
	<span class="token keyword">for</span> <span class="token boolean">_</span><span class="token punctuation">,</span> u <span class="token operator">:=</span> <span class="token keyword">range</span> users <span class="token punctuation">{</span>
		<span class="token comment">// 此处占位符要与插入值的个数对应</span>
		valueStrings <span class="token operator">=</span> <span class="token function">append</span><span class="token punctuation">(</span>valueStrings<span class="token punctuation">,</span> <span class="token string">&quot;(?, ?)&quot;</span><span class="token punctuation">)</span>
		valueArgs <span class="token operator">=</span> <span class="token function">append</span><span class="token punctuation">(</span>valueArgs<span class="token punctuation">,</span> u<span class="token punctuation">.</span>Name<span class="token punctuation">)</span>
		valueArgs <span class="token operator">=</span> <span class="token function">append</span><span class="token punctuation">(</span>valueArgs<span class="token punctuation">,</span> u<span class="token punctuation">.</span>Age<span class="token punctuation">)</span>
	<span class="token punctuation">}</span>
	<span class="token comment">// 自行拼接要执行的具体语句</span>
	stmt <span class="token operator">:=</span> fmt<span class="token punctuation">.</span><span class="token function">Sprintf</span><span class="token punctuation">(</span><span class="token string">&quot;INSERT INTO user (name, age) VALUES %s&quot;</span><span class="token punctuation">,</span>
		strings<span class="token punctuation">.</span><span class="token function">Join</span><span class="token punctuation">(</span>valueStrings<span class="token punctuation">,</span> <span class="token string">&quot;,&quot;</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
	<span class="token boolean">_</span><span class="token punctuation">,</span> err <span class="token operator">:=</span> DB<span class="token punctuation">.</span><span class="token function">Exec</span><span class="token punctuation">(</span>stmt<span class="token punctuation">,</span> valueArgs<span class="token operator">...</span><span class="token punctuation">)</span>
	<span class="token keyword">return</span> err
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br></div></div><h4 id="使用sqlx-in实现批量插入"><a href="#使用sqlx-in实现批量插入" class="header-anchor">#</a> 使用sqlx.In实现批量插入</h4> <p>前提是需要我们的结构体实现<code>driver.Valuer</code>接口：</p> <div class="language-go line-numbers-mode"><pre class="language-go"><code><span class="token keyword">func</span> <span class="token punctuation">(</span>u User<span class="token punctuation">)</span> <span class="token function">Value</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">(</span>driver<span class="token punctuation">.</span>Value<span class="token punctuation">,</span> <span class="token builtin">error</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	<span class="token keyword">return</span> <span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token keyword">interface</span><span class="token punctuation">{</span><span class="token punctuation">}</span><span class="token punctuation">{</span>u<span class="token punctuation">.</span>Name<span class="token punctuation">,</span> u<span class="token punctuation">.</span>Age<span class="token punctuation">}</span><span class="token punctuation">,</span> <span class="token boolean">nil</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>使用<code>sqlx.In</code>实现批量插入代码如下：</p> <div class="language-go line-numbers-mode"><pre class="language-go"><code><span class="token comment">// BatchInsertUsers2 使用sqlx.In帮我们拼接语句和参数, 注意传入的参数是[]interface{}</span>
<span class="token keyword">func</span> <span class="token function">BatchInsertUsers2</span><span class="token punctuation">(</span>users <span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token keyword">interface</span><span class="token punctuation">{</span><span class="token punctuation">}</span><span class="token punctuation">)</span> <span class="token builtin">error</span> <span class="token punctuation">{</span>
	query<span class="token punctuation">,</span> args<span class="token punctuation">,</span> <span class="token boolean">_</span> <span class="token operator">:=</span> sqlx<span class="token punctuation">.</span><span class="token function">In</span><span class="token punctuation">(</span>
		<span class="token string">&quot;INSERT INTO user (name, age) VALUES (?), (?), (?)&quot;</span><span class="token punctuation">,</span>
		users<span class="token operator">...</span><span class="token punctuation">,</span> <span class="token comment">// 如果arg实现了 driver.Valuer, sqlx.In 会通过调用 Value()来展开它</span>
	<span class="token punctuation">)</span>
	fmt<span class="token punctuation">.</span><span class="token function">Println</span><span class="token punctuation">(</span>query<span class="token punctuation">)</span> <span class="token comment">// 查看生成的querystring</span>
	fmt<span class="token punctuation">.</span><span class="token function">Println</span><span class="token punctuation">(</span>args<span class="token punctuation">)</span>  <span class="token comment">// 查看生成的args</span>
	<span class="token boolean">_</span><span class="token punctuation">,</span> err <span class="token operator">:=</span> DB<span class="token punctuation">.</span><span class="token function">Exec</span><span class="token punctuation">(</span>query<span class="token punctuation">,</span> args<span class="token operator">...</span><span class="token punctuation">)</span>
	<span class="token keyword">return</span> err
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br></div></div><h4 id="使用namedexec实现批量插入"><a href="#使用namedexec实现批量插入" class="header-anchor">#</a> 使用NamedExec实现批量插入</h4> <p><strong>注意</strong> ：该功能需1.3.1版本以上，并且1.3.1版本目前还有点问题，sql语句最后不能有空格和<code>;</code>，详见<a href="https://github.com/jmoiron/sqlx/issues/690" target="_blank" rel="noopener noreferrer">issues/690<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a>。</p> <p>使用<code>NamedExec</code>实现批量插入的代码如下：</p> <div class="language-go line-numbers-mode"><pre class="language-go"><code><span class="token comment">// BatchInsertUsers3 使用NamedExec实现批量插入</span>
<span class="token keyword">func</span> <span class="token function">BatchInsertUsers3</span><span class="token punctuation">(</span>users <span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token operator">*</span>User<span class="token punctuation">)</span> <span class="token builtin">error</span> <span class="token punctuation">{</span>
	<span class="token boolean">_</span><span class="token punctuation">,</span> err <span class="token operator">:=</span> DB<span class="token punctuation">.</span><span class="token function">NamedExec</span><span class="token punctuation">(</span><span class="token string">&quot;INSERT INTO user (name, age) VALUES (:name, :age)&quot;</span><span class="token punctuation">,</span> users<span class="token punctuation">)</span>
	<span class="token keyword">return</span> err
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>把上面三种方法综合起来试一下：</p> <div class="language-go line-numbers-mode"><pre class="language-go"><code><span class="token keyword">func</span> <span class="token function">main</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
	err <span class="token operator">:=</span> <span class="token function">initDB</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		<span class="token function">panic</span><span class="token punctuation">(</span>err<span class="token punctuation">)</span>
	<span class="token punctuation">}</span>
	<span class="token keyword">defer</span> DB<span class="token punctuation">.</span><span class="token function">Close</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
	u1 <span class="token operator">:=</span> User<span class="token punctuation">{</span>Name<span class="token punctuation">:</span> <span class="token string">&quot;七米&quot;</span><span class="token punctuation">,</span> Age<span class="token punctuation">:</span> <span class="token number">18</span><span class="token punctuation">}</span>
	u2 <span class="token operator">:=</span> User<span class="token punctuation">{</span>Name<span class="token punctuation">:</span> <span class="token string">&quot;q1mi&quot;</span><span class="token punctuation">,</span> Age<span class="token punctuation">:</span> <span class="token number">28</span><span class="token punctuation">}</span>
	u3 <span class="token operator">:=</span> User<span class="token punctuation">{</span>Name<span class="token punctuation">:</span> <span class="token string">&quot;小王子&quot;</span><span class="token punctuation">,</span> Age<span class="token punctuation">:</span> <span class="token number">38</span><span class="token punctuation">}</span>

	<span class="token comment">// 方法1</span>
	users <span class="token operator">:=</span> <span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token operator">*</span>User<span class="token punctuation">{</span><span class="token operator">&amp;</span>u1<span class="token punctuation">,</span> <span class="token operator">&amp;</span>u2<span class="token punctuation">,</span> <span class="token operator">&amp;</span>u3<span class="token punctuation">}</span>
	err <span class="token operator">=</span> <span class="token function">BatchInsertUsers</span><span class="token punctuation">(</span>users<span class="token punctuation">)</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;BatchInsertUsers failed, err:%v\n&quot;</span><span class="token punctuation">,</span> err<span class="token punctuation">)</span>
	<span class="token punctuation">}</span>

	<span class="token comment">// 方法2</span>
	users2 <span class="token operator">:=</span> <span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token keyword">interface</span><span class="token punctuation">{</span><span class="token punctuation">}</span><span class="token punctuation">{</span>u1<span class="token punctuation">,</span> u2<span class="token punctuation">,</span> u3<span class="token punctuation">}</span>
	err <span class="token operator">=</span> <span class="token function">BatchInsertUsers2</span><span class="token punctuation">(</span>users2<span class="token punctuation">)</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;BatchInsertUsers2 failed, err:%v\n&quot;</span><span class="token punctuation">,</span> err<span class="token punctuation">)</span>
	<span class="token punctuation">}</span>

	<span class="token comment">// 方法3</span>
	users3 <span class="token operator">:=</span> <span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token operator">*</span>User<span class="token punctuation">{</span><span class="token operator">&amp;</span>u1<span class="token punctuation">,</span> <span class="token operator">&amp;</span>u2<span class="token punctuation">,</span> <span class="token operator">&amp;</span>u3<span class="token punctuation">}</span>
	err <span class="token operator">=</span> <span class="token function">BatchInsertUsers3</span><span class="token punctuation">(</span>users3<span class="token punctuation">)</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		fmt<span class="token punctuation">.</span><span class="token function">Printf</span><span class="token punctuation">(</span><span class="token string">&quot;BatchInsertUsers3 failed, err:%v\n&quot;</span><span class="token punctuation">,</span> err<span class="token punctuation">)</span>
	<span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br></div></div><h3 id="sqlx-in的查询示例"><a href="#sqlx-in的查询示例" class="header-anchor">#</a> sqlx.In的查询示例</h3> <p>关于<code>sqlx.In</code>这里再补充一个用法，在<code>sqlx</code>查询语句中实现In查询和FIND_IN_SET函数。即实现<code>SELECT * FROM user WHERE id in (3, 2, 1);</code>和<code>SELECT * FROM user WHERE id in (3, 2, 1) ORDER BY FIND_IN_SET(id, '3,2,1');</code>。</p> <h4 id="in查询"><a href="#in查询" class="header-anchor">#</a> in查询</h4> <p>查询id在给定id集合中的数据。</p> <div class="language-go line-numbers-mode"><pre class="language-go"><code><span class="token comment">// QueryByIDs 根据给定ID查询</span>
<span class="token keyword">func</span> <span class="token function">QueryByIDs</span><span class="token punctuation">(</span>ids <span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token builtin">int</span><span class="token punctuation">)</span><span class="token punctuation">(</span>users <span class="token punctuation">[</span><span class="token punctuation">]</span>User<span class="token punctuation">,</span> err <span class="token builtin">error</span><span class="token punctuation">)</span><span class="token punctuation">{</span>
	<span class="token comment">// 动态填充id</span>
	query<span class="token punctuation">,</span> args<span class="token punctuation">,</span> err <span class="token operator">:=</span> sqlx<span class="token punctuation">.</span><span class="token function">In</span><span class="token punctuation">(</span><span class="token string">&quot;SELECT name, age FROM user WHERE id IN (?)&quot;</span><span class="token punctuation">,</span> ids<span class="token punctuation">)</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		<span class="token keyword">return</span>
	<span class="token punctuation">}</span>
	<span class="token comment">// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它</span>
	query <span class="token operator">=</span> DB<span class="token punctuation">.</span><span class="token function">Rebind</span><span class="token punctuation">(</span>query<span class="token punctuation">)</span>

	err <span class="token operator">=</span> DB<span class="token punctuation">.</span><span class="token function">Select</span><span class="token punctuation">(</span><span class="token operator">&amp;</span>users<span class="token punctuation">,</span> query<span class="token punctuation">,</span> args<span class="token operator">...</span><span class="token punctuation">)</span>
	<span class="token keyword">return</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div><h4 id="in查询和find-in-set函数"><a href="#in查询和find-in-set函数" class="header-anchor">#</a> in查询和FIND_IN_SET函数</h4> <p>查询id在给定id集合的数据并维持给定id集合的顺序。</p> <div class="language-go line-numbers-mode"><pre class="language-go"><code><span class="token comment">// QueryAndOrderByIDs 按照指定id查询并维护顺序</span>
<span class="token keyword">func</span> <span class="token function">QueryAndOrderByIDs</span><span class="token punctuation">(</span>ids <span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token builtin">int</span><span class="token punctuation">)</span><span class="token punctuation">(</span>users <span class="token punctuation">[</span><span class="token punctuation">]</span>User<span class="token punctuation">,</span> err <span class="token builtin">error</span><span class="token punctuation">)</span><span class="token punctuation">{</span>
	<span class="token comment">// 动态填充id</span>
	strIDs <span class="token operator">:=</span> <span class="token function">make</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token builtin">string</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">,</span> <span class="token function">len</span><span class="token punctuation">(</span>ids<span class="token punctuation">)</span><span class="token punctuation">)</span>
	<span class="token keyword">for</span> <span class="token boolean">_</span><span class="token punctuation">,</span> id <span class="token operator">:=</span> <span class="token keyword">range</span> ids <span class="token punctuation">{</span>
		strIDs <span class="token operator">=</span> <span class="token function">append</span><span class="token punctuation">(</span>strIDs<span class="token punctuation">,</span> fmt<span class="token punctuation">.</span><span class="token function">Sprintf</span><span class="token punctuation">(</span><span class="token string">&quot;%d&quot;</span><span class="token punctuation">,</span> id<span class="token punctuation">)</span><span class="token punctuation">)</span>
	<span class="token punctuation">}</span>
	query<span class="token punctuation">,</span> args<span class="token punctuation">,</span> err <span class="token operator">:=</span> sqlx<span class="token punctuation">.</span><span class="token function">In</span><span class="token punctuation">(</span><span class="token string">&quot;SELECT name, age FROM user WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?)&quot;</span><span class="token punctuation">,</span> ids<span class="token punctuation">,</span> strings<span class="token punctuation">.</span><span class="token function">Join</span><span class="token punctuation">(</span>strIDs<span class="token punctuation">,</span> <span class="token string">&quot;,&quot;</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
	<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
		<span class="token keyword">return</span>
	<span class="token punctuation">}</span>

	<span class="token comment">// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它</span>
	query <span class="token operator">=</span> DB<span class="token punctuation">.</span><span class="token function">Rebind</span><span class="token punctuation">(</span>query<span class="token punctuation">)</span>

	err <span class="token operator">=</span> DB<span class="token punctuation">.</span><span class="token function">Select</span><span class="token punctuation">(</span><span class="token operator">&amp;</span>users<span class="token punctuation">,</span> query<span class="token punctuation">,</span> args<span class="token operator">...</span><span class="token punctuation">)</span>
	<span class="token keyword">return</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br></div></div><p>当然，在这个例子里面你也可以先使用<code>IN</code>查询，然后通过代码按给定的ids对查询结果进行排序。</p> <p>参考链接：</p> <p><a href="http://jmoiron.github.io/sqlx/" target="_blank" rel="noopener noreferrer">Illustrated guide to SQLX<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></p></div></section> <footer class="page-edit"><!----> <div class="last-updated"><span class="prefix">2021-9-2: </span> <span class="time">9/3/2021, 6:20:18 PM</span></div></footer> <div class="page-nav"><p class="inner"><span class="prev"><a href="/blogs/GO/go_mysql/go_mysql.html" class="prev">
            Go语言操作MySQL
          </a></span> <span class="next"><a href="/blogs/GO/go_mysql/go_sqlx_2.html">
            GO使用sqlx批量插入数据的若干方法
          </a></span></p></div> <div class="comments-wrapper"><!----></div> <ul class="side-bar sub-sidebar-wrapper" style="width:12rem;" data-v-70334359><li class="level-2" data-v-70334359><a href="/blogs/GO/go_mysql/go_sqlx.html#sqlx介绍" class="sidebar-link reco-side-sqlx介绍" data-v-70334359>sqlx介绍</a></li><li class="level-2" data-v-70334359><a href="/blogs/GO/go_mysql/go_sqlx.html#安装sqlx" class="sidebar-link reco-side-安装sqlx" data-v-70334359>安装sqlx</a></li><li class="level-2" data-v-70334359><a href="/blogs/GO/go_mysql/go_sqlx.html#基本使用" class="sidebar-link reco-side-基本使用" data-v-70334359>基本使用</a></li><li class="level-3" data-v-70334359><a href="/blogs/GO/go_mysql/go_sqlx.html#连接数据库" class="sidebar-link reco-side-连接数据库" data-v-70334359>连接数据库</a></li><li class="level-3" data-v-70334359><a href="/blogs/GO/go_mysql/go_sqlx.html#查询" class="sidebar-link reco-side-查询" data-v-70334359>查询</a></li><li class="level-3" data-v-70334359><a href="/blogs/GO/go_mysql/go_sqlx.html#插入、更新和删除" class="sidebar-link reco-side-插入、更新和删除" data-v-70334359>插入、更新和删除</a></li><li class="level-3" data-v-70334359><a href="/blogs/GO/go_mysql/go_sqlx.html#namedexec" class="sidebar-link reco-side-namedexec" data-v-70334359>NamedExec</a></li><li class="level-3" data-v-70334359><a href="/blogs/GO/go_mysql/go_sqlx.html#namedquery" class="sidebar-link reco-side-namedquery" data-v-70334359>NamedQuery</a></li><li class="level-3" data-v-70334359><a href="/blogs/GO/go_mysql/go_sqlx.html#事务操作" class="sidebar-link reco-side-事务操作" data-v-70334359>事务操作</a></li><li class="level-2" data-v-70334359><a href="/blogs/GO/go_mysql/go_sqlx.html#sqlx-in" class="sidebar-link reco-side-sqlx-in" data-v-70334359>sqlx.In</a></li><li class="level-3" data-v-70334359><a href="/blogs/GO/go_mysql/go_sqlx.html#sqlx-in的批量插入示例" class="sidebar-link reco-side-sqlx-in的批量插入示例" data-v-70334359>sqlx.In的批量插入示例</a></li><li class="level-3" data-v-70334359><a href="/blogs/GO/go_mysql/go_sqlx.html#sqlx-in的查询示例" class="sidebar-link reco-side-sqlx-in的查询示例" data-v-70334359>sqlx.In的查询示例</a></li></ul></main> <!----></div></div></div></div><div class="global-ui"><div class="back-to-ceiling" style="right:1rem;bottom:6rem;width:2.5rem;height:2.5rem;border-radius:.25rem;line-height:2.5rem;display:none;" data-v-c6073ba8 data-v-c6073ba8><svg t="1574745035067" viewBox="0 0 1024 1024" version="1.1" xmlns="http://www.w3.org/2000/svg" p-id="5404" class="icon" data-v-c6073ba8><path d="M526.60727968 10.90185116a27.675 27.675 0 0 0-29.21455937 0c-131.36607665 82.28402758-218.69155461 228.01873535-218.69155402 394.07834331a462.20625001 462.20625001 0 0 0 5.36959153 69.94390903c1.00431239 6.55289093-0.34802892 13.13561351-3.76865779 18.80351572-32.63518765 54.11355614-51.75690182 118.55860487-51.7569018 187.94566865a371.06718723 371.06718723 0 0 0 11.50484808 91.98906777c6.53300375 25.50556257 41.68394495 28.14064038 52.69160883 4.22606766 17.37162448-37.73630017 42.14135425-72.50938081 72.80769204-103.21549295 2.18761121 3.04276886 4.15646224 6.24463696 6.40373557 9.22774369a1871.4375 1871.4375 0 0 0 140.04691725 5.34970492 1866.36093723 1866.36093723 0 0 0 140.04691723-5.34970492c2.24727335-2.98310674 4.21612437-6.18497483 6.3937923-9.2178004 30.66633723 30.70611158 55.4360664 65.4791928 72.80769147 103.21549355 11.00766384 23.91457269 46.15860503 21.27949489 52.69160879-4.22606768a371.15156223 371.15156223 0 0 0 11.514792-91.99901164c0-69.36717486-19.13165746-133.82216804-51.75690182-187.92578088-3.42062944-5.66790279-4.76302748-12.26056868-3.76865837-18.80351632a462.20625001 462.20625001 0 0 0 5.36959269-69.943909c-0.00994388-166.08943902-87.32547796-311.81420293-218.6915546-394.09823051zM605.93803103 357.87693858a93.93749974 93.93749974 0 1 1-187.89594924 6.1e-7 93.93749974 93.93749974 0 0 1 187.89594924-6.1e-7z" p-id="5405" data-v-c6073ba8></path><path d="M429.50777625 765.63860547C429.50777625 803.39355007 466.44236686 1000.39046097 512.00932183 1000.39046097c45.56695499 0 82.4922232-197.00623328 82.5015456-234.7518555 0-37.75494459-36.9345906-68.35043303-82.4922232-68.34111062-45.57627738-0.00932239-82.52019037 30.59548842-82.51086798 68.34111062z" p-id="5406" data-v-c6073ba8></path></svg></div><!----><canvas id="vuepress-canvas-cursor"></canvas></div></div>
    <script src="/assets/js/app.7029ddab.js" defer></script><script src="/assets/js/3.ebaa3085.js" defer></script><script src="/assets/js/1.8ce67e8c.js" defer></script><script src="/assets/js/41.007ae34c.js" defer></script>
  </body>
</html>
